﻿using DzInfo.Tools;
using Kingdee.BOS;
using Kingdee.BOS.App.Core;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Computing;
using Kingdee.BOS.Contracts;
using Kingdee.BOS.Core;
using Kingdee.BOS.Core.CommonFilter;
using Kingdee.BOS.Core.DynamicForm;
using Kingdee.BOS.Core.DynamicForm.PlugIn.Args;
using Kingdee.BOS.Core.List.PlugIn;
using Kingdee.BOS.Core.Metadata;
using Kingdee.BOS.Core.Metadata.FieldElement;
using Kingdee.BOS.Core.Metadata.FormElement;
using Kingdee.BOS.Core.Metadata.PreInsertData;
using Kingdee.BOS.Core.Metadata.Util;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Core.ReportFilter;
using Kingdee.BOS.Model.ReportFilter;
using Kingdee.BOS.Orm;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Orm.Metadata;
using Kingdee.BOS.Serialization;
using Kingdee.BOS.ServiceHelper;
using Kingdee.BOS.Util;
using Newtonsoft.Json;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Mail;
using System.Net.Mime;
using System.Text;
using System.Threading.Tasks;

namespace DzInfo.DynamicFormModelPlugIn.Test
{
    [Description("【列表插件】获取账表数据测试"), HotUpdate]
    public class GetReportDataScheduleService : AbstractListPlugIn
    {
        public override void AfterBarItemClick(AfterBarItemClickEventArgs e)
        {
            base.AfterBarItemClick(e);
            if ("tbGetData".EqualsIgnoreCase(e.BarItemKey))
            {
                GetReportDataTest();
                //SaveReportDataParameter parameter = GetParameter();
                //SaveReportData(parameter);
            }
        }
        public DataTable GetSqlRptData(Context ctx, FormMetadata meta)
        {
            DataTable dt = null;
            ISQLReportService service = ServiceFactory.GetSQLReportService(ctx);
            SQLFilterParameter filter = new SQLFilterParameter();
            SysReportForm form = meta.BusinessInfo.GetForm() as SysReportForm;
            try
            {
                foreach (var keyword in form.SQLDataSource.KeyWordList)
                {
                    SQLFilterField filterField = new SQLFilterField();
                    filterField.Caption = keyword.Name;
                    filterField.IsAllowInput = keyword.IsAllowInput;
                    filterField.IsMultiSelect = keyword.IsMultiSelect;
                    filterField.KeyWord = keyword.KeyWord;
                    SQLFilterRow filterRow = new SQLFilterRow(filterField, "2023-5-1", filterField.Caption, false);
                    filter.SQLFilterRows.Add(filterRow);
                }
                SqlReportQueryResult sqlReportQueryResult = service.CreateDirectSqlData(ctx, filter, form.SQLDataSource);

                if (sqlReportQueryResult.DataSource == null)
                {
                    if (sqlReportQueryResult.TableName.IsNullOrEmptyOrWhiteSpace() == false)
                    {
                        dt = DBUtils.ExecuteDataSet(ctx, string.Format("SELECT * FROM {0}", sqlReportQueryResult.TableName)).Tables[0];
                    }
                }
                else
                {
                    dt = sqlReportQueryResult.DataSource;
                }
            }
            catch (Exception ex) { }
            if (dt.Columns.Contains("FIDENTITYID"))
            {
                dt.Columns.Remove("FIDENTITYID");
            }
            return dt;
        }

        private void GetReportDataTest()
        {
            var ctx = this.Context;
            try

            {
                string rptFormId = "POSW_SQLTEST"; //查询的目标报表标识

                string rptFilterFormId = "BOS_SQLReportFilter"; //报表对应的过滤界面标识

                string rptFilterSchemeId = "682af8c41ad252"; //过滤方案ID,没传则为默认方案

                //string rptFormId = "SAL_DetailReport"; //查询的目标报表标识

                //string rptFilterFormId = "SAL_DetailFormFilter"; //报表对应的过滤界面标识

                //string rptFilterSchemeId = "6821f67b39ec5f"; //过滤方案ID,没传则为默认方案



                //获取相关元数据

                var comFilterMetadata = FormMetaDataCache.GetCachedFilterMetaData(ctx);

                var rptMetadata = FormMetaDataCache.GetCachedFormMetaData(ctx, rptFormId);

                var rptFilterMetadata = FormMetaDataCache.GetCachedFormMetaData(ctx, rptFilterFormId);

                var rptType = rptMetadata.BaseObjectId;
                if (rptType == "BOS_SQLReport")
                {
                    var sqlDt = GetSqlRptData(ctx, rptMetadata);
                    // 生成 Excel 字节流
                    byte[] excelBytes = DataTableToExcel(sqlDt);
                    // 发送邮件
                    SendEmailWithAttachment(excelBytes, "49872850@qq.com");
                }
                else
                {
                    //获取报表过滤方案的构建基础，便于后续模型构建

                    var rptFilterServiceProvider = rptFilterMetadata.BusinessInfo.GetForm().GetFormServiceProvider();

                    var rptModel = new SysReportFilterModel();

                    rptModel.SetContext(ctx, rptFilterMetadata.BusinessInfo, rptFilterServiceProvider);

                    rptModel.FormId = rptFilterMetadata.BusinessInfo.GetForm().Id;

                    rptModel.FilterObject.FilterMetaData = comFilterMetadata;

                    rptModel.InitFieldList(rptMetadata, rptFilterMetadata);

                    rptModel.GetSchemeList();

                    //IsNullOrEmptyOrWhiteSpace()方法须using Kingdee.BOS.Util;

                    if (rptFilterSchemeId.IsNullOrEmptyOrWhiteSpace())

                    {

                        rptModel.LoadDefaultScheme();//加载默认过滤方案

                    }

                    else

                    {

                        rptModel.Load(rptFilterSchemeId);

                    }

                    var filterPara = rptModel.GetFilterParameter();

                    //报表参数

                    IRptParams param = new RptParams();

                    param.FormId = rptFilterMetadata.BusinessInfo.GetForm().Id;

                    param.StartRow = 1;

                    param.EndRow = int.MaxValue;

                    param.FilterParameter = filterPara;

                    param.FilterFieldInfo = rptModel.FilterFieldInfo;

                    param.CustomParams = new Dictionary<string, object>();

                    param.CustomParams.Add("OpenParameter", new Dictionary<string, object>());



                    //报表服务参数，用于查询。
                    //var sqlParam = new SQLReportShowParameter();
                    //sqlParam.ReportFilterParameter = (IRegularFilterParameter)param;
                    //sqlParam.Context = ctx;
                    //sqlParam.PageId = Guid.NewGuid().ToString();
                    var reportServiceParam = new ReportServiceParameter();

                    reportServiceParam.RptFilterParams = param;

                    reportServiceParam.Context = ctx;

                    reportServiceParam.PageId = Guid.NewGuid().ToString();

                    reportServiceParam.BusinessInfo = rptMetadata.BusinessInfo;

                    //var extData = new SQLReportService().GetData(ctx, "", 1, int.MaxValue);

                    List<BaseDataField> baseDataFields = new List<BaseDataField>();
                    List<DateField> dateFields = new List<DateField>();
                    List<ComboField> comboFields = new List<ComboField>();
                    var typeList = rptMetadata.BusinessInfo.Elements;
                    foreach (var type in typeList)
                    {
                        var item = type.GetType();
                        var itemName = type.Name;
                        var itemKey = type.Key;
                        var typeName = item.Name;
                        if (typeName.Equals("BaseDataField"))
                        {
                            baseDataFields.Add(type as BaseDataField);
                        }
                        if (typeName.Equals("DateField"))
                        {
                            dateFields.Add(type as DateField);
                        }
                        if (typeName.Equals("ComboField"))
                        {
                            comboFields.Add(type as ComboField);
                        }
                    }
                    //var newList = baseList.Distinct().ToList();
                    //下一句的resutl.DataSource报表数据的DataTable，物理表名是resutl.DataSource.TableName，可将期数据转储到特定表进行二次加工
                    var rh = param.FilterParameter.ColumnInfo;
                    //List<string>aa= new List<string>();
                    //var exdata = RptExportServiceHelper.ExportRpt(ctx, aa.ToArray(), Kingdee.BOS.KDSReportCommon.Enums.KDSEnums.RptExportType.Excel, "");
                    var reportData = SysReportServiceHelper.GetReportData(reportServiceParam);

                    var dataf = reportData.DataSource;
                    List<string> colList = new List<string>();
                    DataTable newDt = new DataTable();
                    foreach (DataColumn item in dataf.Columns)
                    {
                        foreach (var rname in rh)
                        {
                            if (item.ColumnName.ToUpper() == rname.Key.ToUpper())
                            {
                                item.Caption = rname.Caption;
                                colList.Add(item.ColumnName);
                            }
                        }
                    }
                    for (int i = reportData.DataSource.Columns.Count - 1; i >= 0; i--)
                    {
                        int index = colList.IndexOf(reportData.DataSource.Columns[i].ColumnName);
                        if (index != -1)
                        {

                        }
                        else
                        {
                            reportData.DataSource.Columns.Remove(reportData.DataSource.Columns[i].ColumnName);
                        }
                    }
                    newDt = reportData.DataSource;
                    DataTable nDt = GetNewDt(newDt, baseDataFields, dateFields, comboFields);
                    // 生成 Excel 字节流
                    byte[] excelBytes = DataTableToExcel(newDt);
                    // 发送邮件
                    SendEmailWithAttachment(excelBytes, "49872850@qq.com");
                }
            }

            catch (Exception ex)

            {
                this.View.ShowErrMessage(ex.Message);
                //Kingdee.BOS.Log.Logger.Info("ABC.XX.PLN", string.Format("物料供需数据抓取失败。{0}，{1}\t\n{2}", computeNo, ex.Message, ex.StackTrace));

            }
        }

        private DataTable GetNewDt(DataTable newDt, List<BaseDataField> baseDataFields, List<DateField> dateFields, List<ComboField> comboFields)
        {
            DataTable dt = new DataTable();
            foreach (BaseDataField field in baseDataFields)
            {
                string columnName = field.Key;
                Type newType = typeof(string);
                if (newDt.Columns.Contains(columnName))
                {
                    // 保存原列的位置
                    int originalOrdinal = newDt.Columns[columnName].Ordinal;
                    // 1. 创建临时新列（避免名称冲突）
                    string tempColumnName = $"{columnName}_New";
                    DataColumn newColumn = new DataColumn(tempColumnName, newType);
                    newColumn.Caption = field.Name;
                    newDt.Columns.Add(newColumn);
                    // 2. 迁移数据（处理转换异常）
                    foreach (DataRow row in newDt.Rows)
                    {
                        try
                        {
                            // 尝试将原列值转换为新类型
                            object originalValue = row[columnName];
                            if (originalValue != DBNull.Value && originalValue != null)
                            {
                                var lookObj = field.LookUpObject;
                                string formId = lookObj.FormId;
                                string rValue = row[field.Key].ToString();
                                // 修改列内容
                                row[tempColumnName] = GetNewValue(rValue, formId);
                                // 使用 Convert.ChangeType 进行安全转换
                                //row[tempColumnName] = Convert.ChangeType(rValue, newType);
                            }
                            else
                            {
                                row[tempColumnName] = DBNull.Value;  // 处理空值
                            }
                        }
                        catch (Exception ex)
                        {
                            // 自定义错误处理（例如记录日志或设置默认值）
                            //Console.WriteLine($"转换失败: 行 {row["ID"]}, 值 '{originalValue}'. 错误: {ex.Message}");
                            row[tempColumnName] = DBNull.Value;  // 设为空或默认值
                        }
                    }
                    // 3. 删除旧列
                    newDt.Columns.Remove(columnName);
                    // 4. 重命名新列并调整位置（可选）
                    newColumn.ColumnName = columnName;
                    newColumn.SetOrdinal(originalOrdinal); // 直接使用保存的位置
                }
            }
            foreach (DateField field in dateFields)
            {
                string columnName = field.Key;
                Type newType = typeof(string);
                if (newDt.Columns.Contains(columnName))
                {
                    // 保存原列的位置
                    int originalOrdinal = newDt.Columns[columnName].Ordinal;
                    // 1. 创建临时新列（避免名称冲突）
                    string tempColumnName = $"{columnName}_New";
                    DataColumn newColumn = new DataColumn(tempColumnName, newType);
                    newColumn.Caption = field.Name;
                    newDt.Columns.Add(newColumn);
                    // 2. 迁移数据（处理转换异常）
                    foreach (DataRow row in newDt.Rows)
                    {
                        try
                        {
                            // 尝试将原列值转换为新类型
                            DateTime originalValue = (DateTime)row[columnName];
                            row[tempColumnName] = originalValue.ToShortDateString();
                        }
                        catch (Exception ex)
                        {
                            // 自定义错误处理（例如记录日志或设置默认值）
                            //Console.WriteLine($"转换失败: 行 {row["ID"]}, 值 '{originalValue}'. 错误: {ex.Message}");
                            row[tempColumnName] = DBNull.Value;  // 设为空或默认值
                        }
                    }
                    // 3. 删除旧列
                    newDt.Columns.Remove(columnName);
                    // 4. 重命名新列并调整位置（可选）
                    newColumn.ColumnName = columnName;
                    newColumn.SetOrdinal(originalOrdinal); // 直接使用保存的位置
                }
            }
            foreach (ComboField field in comboFields)
            {
                string columnName = field.Key;
                Type newType = typeof(string);
                if (newDt.Columns.Contains(columnName))
                {
                    // 保存原列的位置
                    int originalOrdinal = newDt.Columns[columnName].Ordinal;
                    // 1. 创建临时新列（避免名称冲突）
                    string tempColumnName = $"{columnName}_New";
                    DataColumn newColumn = new DataColumn(tempColumnName, newType);
                    newColumn.Caption = field.Name;
                    newDt.Columns.Add(newColumn);
                    // 2. 迁移数据（处理转换异常）
                    foreach (DataRow row in newDt.Rows)
                    {
                        try
                        {
                            // 尝试将原列值转换为新类型
                            object originalValue = row[columnName];
                            if (originalValue != DBNull.Value && originalValue != null)
                            {
                                var lookObj = field.EnumObject;
                                string rValue = "";
                                var iList = lookObj["Items"] as DynamicObjectCollection;
                                foreach (var item in iList)
                                {
                                    var str = JsonConvert.SerializeObject(item["Value"]);
                                    string strCaption = item["Caption"].GetString();
                                    if (str == JsonConvert.SerializeObject(originalValue))
                                    {
                                        rValue = strCaption;
                                    }
                                }

                                // 修改列内容
                                row[tempColumnName] = rValue;
                                // 使用 Convert.ChangeType 进行安全转换
                                //row[tempColumnName] = Convert.ChangeType(rValue, newType);
                            }
                            else
                            {
                                row[tempColumnName] = DBNull.Value;  // 处理空值
                            }
                        }
                        catch (Exception ex)
                        {
                            // 自定义错误处理（例如记录日志或设置默认值）
                            //Console.WriteLine($"转换失败: 行 {row["ID"]}, 值 '{originalValue}'. 错误: {ex.Message}");
                            row[tempColumnName] = DBNull.Value;  // 设为空或默认值
                        }
                    }
                    // 3. 删除旧列
                    newDt.Columns.Remove(columnName);
                    // 4. 重命名新列并调整位置（可选）
                    newColumn.ColumnName = columnName;
                    newColumn.SetOrdinal(originalOrdinal); // 直接使用保存的位置
                }
            }
            dt = newDt;
            return dt;
        }

        private string GetEnumName(string enuId)
        {
            string sql = string.Format(@"/*dialect*/ 
            select FCAPTION from T_META_FORMENUMITEM_L where FENUMID='{0}' ", enuId);
            DynamicObjectCollection doc = DBUtils.ExecuteDynamicObject(this.Context, sql);
            if (doc.Count > 0)
            {
                return doc[0][0].ToString();
            }
            return "";
        }

        private object GetNewValue(string rValue, string formId)
        {
            var obj = KingdeeTools.GetDynamicObjectByFormIdAndId(this.Context, formId, rValue);
            string name = obj["name"].ToString();
            return name;
        }

        private void SaveReportData(SaveReportDataParameter parameter, string typeId = "0")
        {
            var ctx = GetContext(this.Context, parameter.UserName);
            // 读取账表数据（存货收发存汇总表）
            var reportData = GetReportData(ctx, parameter);
            var reporHa = reportData.ReportHeader;
            var dataRows = reportData.DataSource.Rows;
            var a = reportData.IsAssignReportHeader;
            //var t = reportData.DataSource.GetPropertyValue("FDATE");
            // 将账表数据保存到指定的表中
            #region 方式1：直接在数据库拷贝一份，如果不再加工数据，推荐用此方式
            if (typeId == "0")
            {
                var sql = string.Format(@"/*dialect*/
                IF EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'{0}') AND type IN (N'U'))
                DROP TABLE {0}
                SELECT * INTO {0} FROM {1}", parameter.TableName, reportData.DataSource.TableName);
                DBUtils.Execute(ctx, sql);
            }
            #endregion
            #region 方式2：使用内存表批量插入数据库
            else if (typeId == "1")
            {
                // 创建临时表
                var createTableSql = BuildCreateTableSql(reportData.DataSource, parameter.TableName);
                DBUtils.Execute(ctx, createTableSql);
                // 将账表数据存入临时表
                var dataSource = reportData.DataSource.Clone();
                dataSource.TableName = parameter.TableName;
                foreach (DataRow row in reportData.DataSource.Rows)
                {
                    dataSource.Rows.Add(row.ItemArray);
                }
                DBUtils.BulkInserts(ctx, dataSource);
            }
            #endregion
        }

        private string BuildCreateTableSql(DataTable dt, string tableName)
        {
            var columnInfos = GetColumnInfos(dt);
            var sql = string.Format(@"/*dialect*/
            IF EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'{0}') AND type IN (N'U'))
            DROP TABLE {0}
            CREATE TABLE {0} ({1})", tableName, string.Join(",", columnInfos.Select(o => o.Item1 + " " + o.Item2)));
            return sql;
        }

        private List<Tuple<string, string>> GetColumnInfos(DataTable dt)
        {
            var list = new List<Tuple<string, string>>();
            foreach (DataColumn column in dt.Columns)
            {
                list.Add(new Tuple<string, string>(column.ColumnName, GetDbType(column.DataType)));
            }
            return list;
        }

        private string GetDbType(Type type)
        {
            if (type == typeof(int) || type == typeof(short))
            {
                return "INT";
            }
            if (type == typeof(long))
            {
                return "BIGINT";
            }
            if (type == typeof(decimal) || type == typeof(float) || type == typeof(double))
            {
                return "DECIMAL(23,10)";
            }
            if (type == typeof(bool))
            {
                return "BIT";
            }
            if (type == typeof(DateTime))
            {
                return "DATETIME";
            }
            if (type == typeof(byte[]))
            {
                return "IMAGE";
            }
            return "NVARCHAR(4000)";
        }

        private IReportData GetReportData(Context ctx, SaveReportDataParameter parameter)
        {
            var metaDataService = new MetaDataService();
            var filterMetadata = new CommonFilterService().GetFilterMetaData(ctx, ""); //加载过滤元数据。
            var reportMetadata = (FormMetadata)metaDataService.Load(ctx, parameter.RptFormId); //加载账表元数据
            var fileList = reportMetadata.BusinessInfo.Elements;
            var reportFilterMetadata = (FormMetadata)metaDataService.Load(ctx, parameter.RptFilterFormId); //加载账表的过滤窗体的元数据。
            var reportFilterServiceProvider = reportFilterMetadata.BusinessInfo.GetForm().GetFormServiceProvider();
            var model = new SysReportFilterModel();
            model.SetContext(ctx, reportFilterMetadata.BusinessInfo, reportFilterServiceProvider);
            model.FormId = reportFilterMetadata.BusinessInfo.GetForm().Id;
            model.FilterObject.FilterMetaData = filterMetadata;
            model.InitFieldList(reportMetadata, reportFilterMetadata);
            model.GetSchemeList();
            var entity = model.Load(parameter.SchemeId);
            var dyn = DeserializeCustomFilter(reportFilterMetadata.BusinessInfo, entity.CustomFilterSetting);
            model.DataObject = dyn;
            var filter = model.GetFilterParameter();
            IRptParams rptParams = new RptParams();
            rptParams.FormId = reportMetadata.BusinessInfo.GetForm().Id;
            rptParams.FilterParameter = filter;
            rptParams.FilterFieldInfo = model.FilterFieldInfo;
            rptParams.ParameterData = GetUserParamters(ctx, reportMetadata.BusinessInfo);
            rptParams.IsOnlyQuerySumData = GetOnlyDspSumData(rptParams.ParameterData);
            rptParams.CurrentPosition = 0;
            rptParams.StartRow = 1;
            rptParams.EndRow = int.MaxValue;
            var rh = rptParams.FilterParameter.ColumnInfo;
            var param = new ReportServiceParameter(ctx, reportMetadata.BusinessInfo, Guid.NewGuid().ToString(), rptParams);
            //var reportData = new SysReportService().GetReportData(param);
            var reportData = SysReportServiceHelper.GetReportData(param);
            var dataf = reportData.DataSource;
            List<string> colList = new List<string>();
            DataTable newDt = new DataTable();
            foreach (DataColumn item in dataf.Columns)
            {
                foreach (var rname in rh)
                {
                    if (item.ColumnName.ToUpper() == rname.Key.ToUpper())
                    {
                        item.Caption = rname.Caption;
                        //DataColumn newCo = new DataColumn();
                        //newCo.ColumnName = item.ColumnName;
                        //newCo.Caption = rname.Caption;
                        //newDt.Columns.Add(newCo);
                        colList.Add(item.ColumnName);
                    }
                }
            }
            for (int i = reportData.DataSource.Columns.Count - 1; i >= 0; i--)
            {
                int index = colList.IndexOf(reportData.DataSource.Columns[i].ColumnName);
                if (index != -1)
                {

                }
                else
                {
                    reportData.DataSource.Columns.Remove(reportData.DataSource.Columns[i].ColumnName);
                }
            }
            newDt = reportData.DataSource;
            // 生成 Excel 字节流
            byte[] excelBytes = DataTableToExcel(newDt);
            // 发送邮件
            SendEmailWithAttachment(excelBytes, "49872850@qq.com");
            return reportData;
        }

        private void SendEmailWithAttachment(byte[] excelData, string recipientEmail)
        {
            try
            {
                var fromAddress = new MailAddress("it@dna-group.com", "Data Report");
                var toAddress = new MailAddress(recipientEmail);

                using (var message = new MailMessage(fromAddress, toAddress))
                {
                    message.Subject = "Data Report Attachment";
                    message.Body = "请查收附件中的 Excel 文件。";

                    // 添加附件
                    var stream = new MemoryStream(excelData);
                    var attachment = new Attachment(stream, "Report.xlsx", MediaTypeNames.Application.Octet);
                    message.Attachments.Add(attachment);

                    // 配置 SMTP
                    using (var smtp = new SmtpClient("smtp.qiye.aliyun.com", 25))
                    {
                        smtp.Credentials = new NetworkCredential("it@dna-group.com", "Dna111222");
                        smtp.EnableSsl = false; // 根据 SMTP 服务器要求启用 SSL
                        smtp.Send(message);
                    }
                }

                Console.WriteLine("邮件发送成功！");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"发送失败: {ex.Message}");
            }
        }


        private byte[] DataTableToExcel(DataTable dt)
        {
            using (var package = new ExcelPackage())
            {
                var worksheet = package.Workbook.Worksheets.Add("Sheet1");

                // 写入列头
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1].Value = dt.Columns[i].Caption;
                    worksheet.Cells[1, i + 1].Style.Font.Bold = true; // 加粗标题
                    worksheet.Cells[1, i + 1].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    worksheet.Cells[1, i + 1].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
                }

                // 写入数据
                for (int row = 0; row < dt.Rows.Count; row++)
                {
                    for (int col = 0; col < dt.Columns.Count; col++)
                    {
                        worksheet.Cells[row + 2, col + 1].Value = dt.Rows[row][col];
                    }
                }

                // 自动调整列宽
                worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();

                // 返回 Excel 文件的字节流
                return package.GetAsByteArray();
            }
        }

        private bool GetOnlyDspSumData(DynamicObject userParameterData)
        {
            var result = false;
            if (userParameterData != null && userParameterData.DynamicObjectType.Properties.ContainsKey(KeyConst.FDSPSUMDATA_KEY))
            {
                result = (bool)userParameterData[KeyConst.FDSPSUMDATA_KEY];
            }
            return result;
        }

        private DynamicObject GetUserParamters(Context ctx, BusinessInfo reportBusinessInfo)
        {
            string formId = reportBusinessInfo.GetForm().ParameterObjectId;
            if (formId == null || formId.Trim().Length == 0)
            {
                formId = FormIdConst.BOS_ReportUserParameter;
            }
            var formMetadata = (FormMetadata)new MetaDataService().Load(ctx, formId);
            return new UserParameterService().Load(ctx, formMetadata.BusinessInfo, ctx.UserId, reportBusinessInfo.GetForm().Id);
        }

        private DynamicObject DeserializeCustomFilter(BusinessInfo businessInfo, string xml)
        {
            var binder = new DynamicObjectDcxmlBinder(businessInfo);
            binder.OnlyDbProperty = false;
            var target = new DcxmlSerializer(binder);
            binder.Culture = CultureInfo.InvariantCulture;
            target.ColloctionIgnorePKValue = true;
            var obj = (DynamicObject)target.DeserializeFromString(xml);
            return obj;
        }

        private Context GetContext(Context ctx, string userName)
        {
            if (userName.IsNullOrEmpty())
            {
                return ctx;
            }
            var userId = DBUtils.ExecuteScalar(ctx, string.Format("SELECT FUSERID FROM T_SEC_USER WHERE FNAME='{0}'", userName), 0);
            if (userId <= 0)
            {
                return ctx;
            }
            var newCtx = (Context)ctx.Clone();
            newCtx.UserId = userId;
            newCtx.UserName = userName;
            return newCtx;
        }

        private SaveReportDataParameter GetParameter()
        {
            SaveReportDataParameter par = new SaveReportDataParameter();
            par.UserName = "demo";
            par.RptFormId = "PUR_RPT_POOrderTrace";
            par.RptFilterFormId = "PUR_RPT_POOrderTraceFilter";
            par.SchemeId = "682acb226cc4fa";
            par.TableName = "采购订单执行明细表";
            return par;
        }

        /// <summary>
        /// 账表数据保存参数
        /// </summary>
        public class SaveReportDataParameter
        {
            /// <summary>
            /// 用户名(登录账号)
            /// </summary>
            public string UserName { get; set; }
            /// <summary>
            /// 账表FormId
            /// </summary>
            public string RptFormId { get; set; }
            /// <summary>
            /// 账表的过滤窗体的FormId
            /// </summary>
            public string RptFilterFormId { get; set; }
            /// <summary>
            /// 过滤方案内码
            /// </summary>
            public string SchemeId { get; set; }
            /// <summary>
            /// 存储账表数据的表名
            /// </summary>
            public string TableName { get; set; }
        }
    }
}
